const { getResult } = require("./sql");

class SqlSearch {
  constructor(db, res) {
    this.db = db;
    this.res = res;
  }

  // 1: %param, 2: %param%, 3: param%
  transformParam(str, type) {
    if (type === 1) {
      return `%${str}`;
    } else if (type === 2) {
      return `%${str}%`;
    } else {
      return `${str}%`;
    }
  }

  /**
   *
   * @param { 参数数组, 若有模糊查询的项则为object } propList
   * @param { 值数组, 同上 } valueList
   * @returns
   */
  async search(propList, valueList) {
    let sql = `SELECT * FROM ${this.db} WHERE 1=1`;
    let paramsList = [];
    if (propList && propList.length !== 0) {
      let conditions = propList
        .map((item) =>
          typeof item === "object" ? `${item.prop} LIKE ?` : `${item}=?`
        )
        .join(" AND ");
      sql += ` AND ${conditions}`;

      valueList.forEach((item) => {
        if (typeof item === "object") {
          paramsList.push(this.transformParam(item.value, item.type));
        } else {
          paramsList.push(item);
        }
      });
    }
    let result = await getResult({ sql, paramsList }, this.res);

    // 暴露sql供存在sql调用
    return {
      data: result,
      sql,
    };
  }

  async add(propList, valueList) {
    let sql = `
    INSERT INTO ${this.db} 
    (${propList.join(",")})
    VALUE 
    (${propList.map((item) => "?").join(",")})
    `;
    await getResult({ sql, paramsList: valueList });
  }

  async del(params, paramsProp = "id") {
    let sql = `
    DELETE FROM ${this.db} WHERE ${paramsProp}=?
    `;
    let paramsList = [params];
    await getResult({ sql, paramsList });
  }

  async existSql(propList, valueList) {
    let sql = `
    SELECT EXISTS
    (${this.search(propList, valueList).sql}) 
    as exist`;

    let paramsList = [];

    let result = await getResult({ sql, paramsList });

    return result[0][0].exist === 1;
  }

  // 左连接（Left Join）：保留左表的所有数据，删除右表中没有匹配的数据。
  // 右连接（Right Join）：保留右表的所有数据，删除左表中没有匹配的数据。
  // 内连接（Inner Join）：只保留两个表中连接键匹配的数据。
  // Union与Union All的区别：Union会去除重复记录，而Union All则会保留所有记录。

  /**
   * 联表查询 demo
   * 查询出daily_task中create_time在4-20号之后的数据
   * 同时根据每一行数据的user_id找出他们的name
   * 还要根据该id找出yearly_plan表中所有的goal
   * */

   // 查询yearly_plan中所有数据的goal
  //  daily_task date为2025-01-02的content
  //  weekly_task中user_id为2的task
  // 返回的字段名是goal
//   let sql = `
//   SELECT
//     yearly_plan.goal
//   FROM yearly_plan

//   UNION ALL
//   SELECT
//     daily_task.status AS goal
//   FROM
//     daily_task
//   WHERE
//     date=?
//   UNION ALL
//   SELECT
//     weekly_task.task
//   FROM
//     weekly_task
//   WHERE
//     user_id=?
// `;
  async joinSearch() {
    let sql = `
  SELECT
    dt.*,
    u.account,
    yp.goal
  FROM
    daily_task dt
  LEFT JOIN
    user u ON dt.user_id = u.user_id
  LEFT JOIN
    yearly_plan yp ON dt.user_id = yp.user_id
  WHERE
    dt.create_time BETWEEN '2025-04-20 00:00:00' AND '2025-05-20 00:00:00'
`;

    let paramsList = [15];
  }
}

module.exports = SqlSearch;
